﻿/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

PRINT 'Create dbwriter server login'
IF NOT EXISTS(SELECT name FROM [master].[sys].[syslogins] WHERE NAME = 'dbwriter')
CREATE LOGIN dbwriter WITH PASSWORD = 'Password1234!';
GO

PRINT 'Create dbreader server login'
IF NOT EXISTS(SELECT name FROM [master].[sys].[syslogins] WHERE NAME = 'dbreader')
CREATE LOGIN dbreader WITH PASSWORD = 'Password1234!';
GO

PRINT 'Create dbwriter database login'
IF NOT EXISTS(SELECT name FROM [sysusers] WHERE NAME = 'dbwriter')
CREATE USER dbwriter FOR LOGIN dbwriter;
GO

PRINT 'Create dbreader database login'
IF NOT EXISTS(SELECT name FROM [sysusers] WHERE NAME = 'dbreader')
CREATE USER dbreader FOR LOGIN dbreader;
GO

PRINT 'Grant Read/Write Access on the database to dbwriter'
EXEC sp_addrolemember db_datareader, dbwriter
EXEC sp_addrolemember db_datawriter, dbwriter
GO

PRINT 'Grant SP Execute on the database to dbwriter'
DECLARE @login VARCHAR(50)
SET @login = 'dbwriter'
DECLARE @sql VARCHAR(max)
SELECT @sql = Coalesce(@sql, '') + 'GRANT EXEC ON [' + [name] + '] TO [' + @login + ']' + Char(13)
FROM sysobjects WHERE xtype = 'P' AND [name] NOT LIKE 'dt_%'
print @sql
EXEC (@sql)

GO

PRINT 'Grant Read Access on the database to dbreader'
EXEC sp_addrolemember db_datareader, dbwriter
GO

PRINT 'Grant SP Execute on the database to dbreader'
DECLARE @login VARCHAR(50)
SET @login = 'dbreader'
DECLARE @sql VARCHAR(max)
SELECT @sql = Coalesce(@sql, '') + 'GRANT EXEC ON [' + [name] + '] TO [' + @login + ']' + Char(13)
FROM sysobjects WHERE xtype = 'P' AND [name] NOT LIKE 'dt_%'
print @sql
EXEC (@sql)

GO

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT
GO
CREATE FULLTEXT INDEX ON Announcements.Announcements(Title, ShortDescription, LongDescription) KEY INDEX Pk_Announcements WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON Console.Users(FirstName,LastName,Email) KEY INDEX Pk_Users WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON Files.Files(Url,Content TYPE COLUMN Extension) KEY INDEX Pk_Files WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON Pages.Pages(Title,Url) KEY INDEX Pk_Pages WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON PressReleases.PressReleases(Title,ShortDescription,LongDescription) KEY INDEX Pk_PressReleases WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON Classifieds.Postings(Title,Price,Location,Description) KEY INDEX Pk_Postings WITH CHANGE_TRACKING AUTO;
CREATE FULLTEXT INDEX ON Businesses.Businesses(Name,Street,Street2,City,State,Zip,Phone,Description) KEY INDEX Pk_Businesses WITH CHANGE_TRACKING AUTO;
GO

--------- CONTENT SCRIPTS-------------